import sqlite3
import hashlib
import os
from datetime import datetime


def init_db():
    """初始化数据库，创建用户表"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    # 创建用户表
    c.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        email TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP
    )
    ''')

    conn.commit()
    conn.close()


def hash_password(password):
    """使用SHA-256哈希密码"""
    return hashlib.sha256(password.encode()).hexdigest()


def create_user(username, password, email=None):
    """创建新用户"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    password_hash = hash_password(password)

    try:
        c.execute(
            "INSERT INTO users (username, password_hash, email) VALUES (?, ?, ?)",
            (username, password_hash, email)
        )
        conn.commit()
        result = True
    except sqlite3.IntegrityError:
        result = False  # 用户名已存在
    except Exception as e:
        print(f"Error creating user: {e}")
        result = False
    finally:
        conn.close()

    return result


def verify_user(username, password):
    """验证用户凭据"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    password_hash = hash_password(password)

    c.execute(
        "SELECT id, username FROM users WHERE username = ? AND password_hash = ?",
        (username, password_hash)
    )

    user = c.fetchone()

    if user:
        # 更新最后登录时间
        c.execute(
            "UPDATE users SET last_login = ? WHERE id = ?",
            (datetime.now(), user[0])
        )
        conn.commit()

    conn.close()

    return user is not None


def username_exists(username):
    """检查用户名是否存在"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    c.execute("SELECT id FROM users WHERE username = ?", (username,))
    result = c.fetchone() is not None

    conn.close()
    return result


def email_exists(email):
    """检查邮箱是否存在"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    c.execute("SELECT id FROM users WHERE email = ?", (email,))
    result = c.fetchone() is not None

    conn.close()
    return result


def get_user_count():
    """获取用户数量"""
    conn = sqlite3.connect('users.db')
    c = conn.cursor()

    c.execute("SELECT COUNT(*) FROM users")
    count = c.fetchone()[0]

    conn.close()
    return count